/*** TABLES AND FIGURES -- MOBILITY SAMPLE ***/

clear all
set more 1

do "paths.do"

capture log close
log using "$LOGFILES\track_mob_3.log", replace

/************************************************************
Table 7 and 8: mobility mean outcomes
*************************************************************/

local k = 0
foreach xx in unadj rel {
	foreach ss in samp0 samp1 samp2 samp3 {
		di "`xx' `ss'"
		use "$WORKING/track_mob_E.dta", clear
		gen samp2 = samp2_`xx'
	
		keep if `ss'
		
		collapse (mean) pred_*_half
		gen id = 1
		reshape long pred_, i(id) j(var_name) string
		gen meas = "`xx'"
		gen samp = "`ss'"
		
		if (`k' > 0) {
			append using "$WORKING\temp/track_mob_3-t7t8-means.dta"
		}
		save "$WORKING\temp/track_mob_3-t7t8-means.dta", replace
		local k = `k' + 1
	}
}

export delimited using "$WORKING\track_mob_3-t7t8-means.csv", replace

/************************************************************
Fig C9: tracking exposure residual distribution
Table C1: tracking exposure residual sd
Table C2: tracking exposure variation accounted for by
cohort grade 3 score distribution

All of these deal with treatment variation in the mobility
sample.
*************************************************************/

global CTRLVARS1 cy_score_mn cy_score_sd
global CTRLVARS2 has_year1 has_year2 has_year3 has_year4
global CTRLFLEX cy_score_p10 cy_score_p25 cy_score_p75 cy_score_p90

use "$WORKING/tracking_10-steps_E.dta", clear

** only 2011 and 2015 grade 4 cohorts
keep if samp1 == 1
tab base_year

keep trk_*_*_cy_dm trk_*_*_dy_am base_campus base_year base_district obs $CTRLVARS1 $CTRLVARS2 $CTRLFLEX
foreach vv in unadj rel {
	foreach xx in cy_dm dy_am {
		gen trk_`vv'_comb_`xx' = (0.4 * trk_`vv'_elem_`xx') + (0.6 * trk_`vv'_mid_`xx')
	}
}
** residualize tracking
foreach vv in unadj rel {
	foreach xx in cy_dm dy_am {
		foreach gg in comb elem mid {
			reghdfe trk_`vv'_`gg'_`xx' $CTRLVARS1 $CTRLVARS2 $CTRLFLEX [aw=obs], ///
					absorb(base_year base_campus) resid
			predict trktil_`vv'_`gg'_`xx', residuals
			drop _reg*
		}
	}
}

save "$WORKING/temp/track_mob_3-fc9-0.dta", replace

** 2015 minus 2011, differences in residuals
keep trk_* trktil* base_campus base_year base_district obs 
gen one0 = 1
reshape wide trk_* trktil_* one0 obs, i(base_campus base_district) j(base_year)
tab one02011 one02015, missing
drop if (one02011 == .) | (one02015 == .)
foreach vv in unadj rel {
	foreach xx in cy_dm dy_am {
		foreach gg in comb elem mid {
			foreach tt in trk trktil {
				gen tdiff_`tt'_`vv'_`gg'_`xx' = `tt'_`vv'_`gg'_`xx'2015 - `tt'_`vv'_`gg'_`xx'2011
			}
		}
	}
}

save "$WORKING/temp/track_mob_3-fc9-1.dta", replace


** standard deviations of all tracking exposure measures
use "$WORKING/temp/track_mob_3-fc9-1.dta", clear
collapse (sd) tdiff_*
xpose, clear varname
** organize measures
split _varname, parse("_")
rename (v1 _varname2 _varname3 _varname4) (trk_sd resid_type trk_type grade_level)
gen geog_type = "district"
replace geog_type = "campus" if _varname5 == "cy"
drop _varnam*
sort trk_type grade_level geog_type resid_type
order trk_type grade_level geog_type resid_type trk_sd
export delimited using "$OUTPUT\track_mob_3-tc1-expsd.csv", replace


** tracking exposure residual distribution
local k = 0
foreach vv in unadj rel {
	foreach gg in comb elem mid {
		foreach xx in cy_dm dy_am {
			foreach tt in trk trktil {
				use "$WORKING/temp/track_mob_3-fc9-1.dta", clear
				rename tdiff_`tt'_`vv'_`gg'_`xx' t0
				gen t1 = round(t0 * 20) / 20
				gen obs_avg = (obs2011 + obs2015) / 2
				collapse (sum) nsch_`vv'_`gg'_`xx'_`tt'=obs_avg nstud_`vv'_`gg'_`xx'_`tt'=obs_avg, by(t1)
				if (`k' != 0) {
					merge 1:1 t1 using "$WORKING\temp/track_mob_3-fc9-2.dta"
					drop _merge
				}
				replace nsch_`vv'_`gg'_`xx'_`tt' = . if nsch_`vv'_`gg'_`xx'_`tt' <= 5
				replace nstud_`vv'_`gg'_`xx'_`tt'= . if nstud_`vv'_`gg'_`xx'_`tt' <= 5
				
				egen tmp1 = sum(nsch_`vv'_`gg'_`xx'_`tt')
				gen pctsch_`vv'_`gg'_`xx'_`tt' = nsch_`vv'_`gg'_`xx'_`tt' / tmp1
				egen tmp2 = sum(nstud_`vv'_`gg'_`xx'_`tt')
				gen pctstud_`vv'_`gg'_`xx'_`tt' = nstud_`vv'_`gg'_`xx'_`tt' / tmp2
				drop tmp* 
				
				save "$WORKING\temp/track_mob_3-fc9-2.dta", replace
				local k = `k' + 1
			}
		}
	}
}
order t1 nsch* pctsch_* nstud* pctstud_*
export delimited using "$OUTPUT\track_mob_3-fc9-expresid.csv", replace


** tracking exposure variation accounted for by cohort grade 3 score distribution
use "$WORKING/temp/track_mob_3-fc9-0.dta", clear
foreach vv in unadj rel {
	foreach gg in comb elem mid {
		foreach xx in cy_dm dy_am {
			reghdfe trk_`vv'_`gg'_`xx' $CTRLVARS2 [aw=obs], ///
						absorb(base_year base_campus) vce(cluster base_district)
			eststo, title("expscore::`vv'::`xx'::`gg'::scoreno")
			reghdfe trk_`vv'_`gg'_`xx' $CTRLVARS2 $CTRLVARS1 $CTRLFLEX [aw=obs], ///
						absorb(base_year base_campus) vce(cluster base_district)
			eststo, title("expscore::`vv'::`xx'::`gg'::scoreyes")
		}
	}
}
esttab using "$OUTPUT/track_mob_3-tc2-expscore.csv", csv b(%9.5f) se(%9.5f) r2 ar2 ///
			sfmt(%9.8g) nostar wide mlabels(,titles) replace
estimates clear


*************************************************
Figure 6: achievement mobility
F6-mob
*************************************************

use "$WORKING/track_mob_D.dta", clear

foreach yg in nm dm {
	foreach vv in trk_unadj trk_rel {
		egen `vv'_cy_`yg' = rowmean(`vv'_`yg'0 `vv'_`yg'1 `vv'_`yg'2 `vv'_`yg'3 `vv'_`yg'4)
	}
}

keep base_campus base_year trk_unadj_cy_nm trk_rel_cy_nm trk_unadj_cy_dm trk_rel_cy_dm

save "$WORKING/temp/track_mob_3-figs_00.dta", replace

use "$WORKING/tracking_10_C.dta", clear
merge m:1 base_campus base_year using "$WORKING/temp/track_mob_3-figs_00.dta"
assert _merge == 3
drop _merge

** loop over trk_unadj and trk_rel
** loop over elem, mid, and all
** loop over individual, campus cohort, district cohort
** loop over outcomes: score3, score4, obs_cls, peer_cls
foreach vv in obs_cls peer_prev peer_init sdpeer_prev sdpeer_init {
	egen `vv'_avg_all = rowmean(`vv'_dm*)
}
egen clssz_raw_avg_all = rowmean(obs_cls0 obs_cls1 obs_cls2 obs_cls3 obs_cls4)


foreach vv in trk_unadj trk_rel {
	egen tmp1 = median(`vv'_cy_dm)
	gen `vv'_cy_dm_hilo = "_lo"
	replace `vv'_cy_dm_hilo = "_hi" if `vv'_cy_dm > tmp1
	drop tmp*
	
	egen tmp1 = pctile(`vv'_cy_dm), p(25)
	egen tmp2 = pctile(`vv'_cy_dm), p(50)
	egen tmp3 = pctile(`vv'_cy_dm), p(75)
	gen `vv'_cy_dm_qrt = "_q1"
	replace `vv'_cy_dm_qrt = "_q2" if `vv'_cy_dm > tmp1
	replace `vv'_cy_dm_qrt = "_q3" if `vv'_cy_dm > tmp2
	replace `vv'_cy_dm_qrt = "_q4" if `vv'_cy_dm > tmp3
	drop tmp*
}

** loop over outcomes: score3, score4, obs_cls, peer_cls
global OUTCOMES mscore_fill_pct3 mscore_fill_pct4 clssz_raw_avg_all ///
		obs_cls_avg_all peer_prev_avg_all peer_init_avg_all sdpeer_prev_avg_all sdpeer_init_avg_all over8 under8

foreach yy in $OUTCOMES {
	egen `yy'_0 = mean(`yy'), by(base_year base_pctfix)
}

gen pct0 = floor(base_pctraw * 50) * 2

gen one0 = 1

keep trk_*_hilo trk_*_qrt pct0 one0 $OUTCOMES trk_unadj_cy_nm trk_rel_cy_nm trk_unadj_cy_dm trk_rel_cy_dm

save "$WORKING/temp/track_mob_3-figs_0.dta", replace




local kk = 0
foreach grp in qrt hilo {
    local k = 0
	** loop over treatments: trk_unadj and trk_rel
	foreach xx in unadj rel {
		use "$WORKING/temp/track_mob_3-figs_0.dta", clear
		collapse (sum) nstud=one0 (mean) trk_`xx'_cy_*m, by(trk_`xx'_cy_dm_`grp')
		rename trk_`xx'_cy_dm_`grp' split_val
		rename trk_`xx'_cy_nm trk_avg_nm
		rename trk_`xx'_cy_dm trk_avg_dm
		
		gen split_var = "trk_`xx'_cy_dm_`grp'"
		
		if (`kk' != 0){
			append using "$WORKING/temp/track_mob_3-figs_3.dta"
		}
		save "$WORKING/temp/track_mob_3-figs_3.dta", replace
		local kk = `kk' + 1

		use "$WORKING/temp/track_mob_3-figs_0.dta", clear
		collapse (sum) nstud=one0 (mean) $OUTCOMES, by(trk_`xx'_cy_dm_`grp' pct0)
		foreach yy in $OUTCOMES {
			replace `yy' = round(`yy' * 1000) / 1000
		}
		reshape wide nstud $OUTCOMES, string i(pct0) j(trk_`xx'_cy_dm_`grp')
		gen x = "trk_`xx'_cy_dm"
		order x pct0 mscore_fill_pct3* mscore_fill_pct4* obs_cls_avg_all* peer_prev_avg_all* ///
				peer_init_avg_all* sdpeer_prev_avg_all* sdpeer_init_avg_all* over8* under8*
		if (`k' != 0){
			append using "$WORKING/temp/track_mob_3-figs_1_`grp'.dta"
		}
		save "$WORKING/temp/track_mob_3-figs_1_`grp'.dta", replace
		local k = `k' + 1
	}
	gen pct1 = pct0 + 1

	rename x split_var

	keep split_var pct1 nstud_* mscore_fill_pct3* mscore_fill_pct4* clssz_raw_avg_all* obs_cls_avg_all* ///
			peer_prev_avg_all* peer_init_avg_all* sdpeer_prev_avg_all* sdpeer_init_avg_all* over8* under8*
	order split_var pct1 nstud_* mscore_fill_pct3* mscore_fill_pct4* clssz_raw_avg_all* obs_cls_avg_all* ///
			peer_prev_avg_all* peer_init_avg_all* sdpeer_prev_avg_all* sdpeer_init_avg_all* over8* under8*
	
	save "$WORKING/track_mob_3-figs_2_`grp'.dta", replace
}


use "$WORKING/temp/track_mob_3-figs_3.dta", clear
export delimited using "$OUTFILES\track_mob_3-figs-avgs.csv", replace

use "$WORKING/track_mob_3-figs_2_hilo.dta", clear
merge 1:1 split_var pct1 using "$WORKING/track_mob_3-figs_2_qrt.dta"
assert _merge == 3
drop _merge
export delimited using "$OUTFILES\track_mob_3-f6-mob.csv", replace


*************************************************
Figures C1, C7, C8: status summary ***
*************************************************

use "$WORKING/track_mob_C.dta", clear

keep id2 base_year base_pctraw base_pctfix stat_enrl* stat_score* d* over8 under8 has_year4
drop district

global LEVELVALS left under even over
global STATENRLVALS left retained ontrack
global STATSCOREVALS left curr fill1 fill2p fillN
global DVALS dmale dhisp dblack dwhite dasian dother ddisadv
global AGEVALS age_8m age_9 age_10 age_11p

gen age_8m = dage <= 8
gen age_9 = dage == 9
gen age_10 = dage == 10
gen age_11p = dage >= 11
drop dage

gen tmp1 = !has_year4
egen stat_level8_left = mean(tmp1), by(base_year base_pctfix)
replace tmp1 = has_year4 & (under8 == 1)
egen stat_level8_under = mean(tmp1), by(base_year base_pctfix)
replace tmp1 = has_year4 & (under8 == 0) & (over8 == 0)
egen stat_level8_even = mean(tmp1), by(base_year base_pctfix)
replace tmp1 = has_year4 & (over8 == 1)
egen stat_level8_over = mean(tmp1), by(base_year base_pctfix)
drop tmp*

foreach yy in 3 4 {
    foreach vv in $STATENRLVALS {
	    gen tmp1 = (stat_enrl`yy' == "`vv'")
	    egen stat_enrl_`vv'_`yy' = mean(tmp1), by(base_year base_pctfix)
		drop tmp*
	}
    foreach vv in $STATSCOREVALS {
	    gen tmp1 = (stat_score`yy' == "`vv'")
	    egen stat_score_`vv'_`yy' = mean(tmp1), by(base_year base_pctfix)
		drop tmp*
	}
}

gen pct0 = floor(base_pctraw * 50) * 2
gen one0 = 1

collapse (sum) obs=one0 (mean) stat_enrl_* stat_score_* stat_level8_* d* age_*, by(pct0)

gen pct1 = pct0 + 1
foreach yy in 3 4 {
    foreach vv in $STATSCOREVALS {
	    gen stat_score0_`vv'_`yy' = stat_score_`vv'_`yy' / (1 - stat_score_left_`yy')
	}
}

foreach yy in 3 4 {
    foreach vv in $STATENRLVALS {
	    replace stat_enrl_`vv'_`yy' = round(stat_enrl_`vv'_`yy' * 1000) / 1000
	}
    foreach vv in $STATSCOREVALS {
	    replace stat_score0_`vv'_`yy' = round(stat_score0_`vv'_`yy' * 1000) / 1000
	}
	drop stat_score0_left_`yy'
}
foreach vv in $LEVELVALS {
	replace stat_level8_`vv' = round(stat_level8_`vv' * 1000) / 1000
}
foreach vv in $DVALS $AGEVALS {
    replace `vv' = round(`vv' * 1000) / 1000
}


keep pct1 obs stat_enrl_*_3 stat_enrl_*_4 stat_score0_*_3 stat_score0_*_4 stat_level8_* d* age_*
order pct1 obs stat_enrl_*_3 stat_enrl_*_4 stat_score0_*_3 stat_score0_*_4 stat_level8_* d* age_*

save "$WORKING/track_mob_3-figs_3.dta", replace
export delimited using "$OUTFILES\track_mob_3-fc1-status.csv", replace

*************************************************
Figure ?: p25 p75 distributions
*************************************************

local k = 0

foreach yy in 1 2 3 4 {
	foreach pp in 25 75 {
	    use "$WORKING/track_mob_E.dta", clear
		keep base_campus base_year pred_`pp'_scoreresid`yy'_half obs

		gen pred_`pp'_`yy'0 = floor(pred_`pp'_scoreresid`yy'_half * 20) / 20
		collapse (sum) obs, by(pred_`pp'_`yy'0)
		rename pred_`pp'_`yy'0 bin0
		replace obs = . if (obs <= 5)
		rename obs pred_`pp'_`yy'
		local k = `k' + 1
		if (`k' > 1){
		    merge 1:1 bin0 using "$WORKING/temp/track_mob_3-figs_p_0.dta"
			drop _merge
		}
		save "$WORKING/temp/track_mob_3-figs_p_0.dta", replace
	}
}

export delimited using "$OUTFILES\track_mob_3-f-predz.csv", replace





log close
